﻿using System;
using System.Data;
using System.Data.DBLib;
using System.Web;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    public class ProjectGroupsDB : DBBase
    {
        /*
         select ProjectGroupID, Title, OfficerName, 
Type, Applicant, Client, Inventor, CreateDate from projectgroup
         */
        public DataTable getProjectGroupsList(string projectGroupID, 
                                                string title, 
                                                string officerName,
                                                string type,
                                                string applicant,
                                                string client,
                                                string inventor,
                                                string createDateBegin,
                                                string createDateEnd,
                                                int isDelete)
        {
            string strSql = "";
            string strOwnerGroupID;
            string strUserType;
            string strUserID;

            strOwnerGroupID = HttpContext.Current.Session[SysUtil.m_str_session_usergroupid].ToString();
            strUserType = HttpContext.Current.Session[SysUtil.m_str_session_usertype].ToString();
            strUserID = HttpContext.Current.Session[SysUtil.m_str_session_loginname].ToString();

            if (ConnectDB())
            {
                strSql = "select * from ProjectGroup where 1=1";

                if (projectGroupID != "" && projectGroupID != null)
                {
                    strSql += " and ProjectGroupID like '%" + projectGroupID + "%'";
                }
                if (title != "" && title != null)
                {
                    strSql += " and Title like '%" + title + "%'";
                }
                if (officerName != "" && officerName != null)
                {
                    strSql += " and OfficerName like '%" + officerName + "%'";
                }
                if (type != "" && type != null)
                {
                    strSql += " and Type='" + type + "'";
                }
                if (applicant != "" && applicant != null)
                {
                    strSql += " and Applicant like '%" + applicant + "%'";
                }
                if (client != "" && client != null)
                {
                    strSql += " and Client='" + client + "'";
                }
                if (inventor != "" && inventor != null)
                {
                    strSql += " and Inventor like '%" + inventor + "%'";
                }
                if (createDateBegin != "" && createDateEnd != "")
                {
                    strSql += " and CreateDate between '" + createDateBegin + "' and '" + createDateEnd + "'"; ;
                }
                strSql += " and IsDelete=" + isDelete;

                strSql += " and (1 in (select GroupId from DBF_USER where LoginName = '";
                strSql += strUserID;
                strSql += "') or 1 in (select UserRole from DBF_USER where LoginName = '";
                strSql += strUserID;
                strSql += "') or OwnerGroupId in (select GroupId from DBF_USER where LoginName = '";
                strSql += strUserID;
                strSql += "'))";

                return m_objDB.getDataTable(strSql, "ProjectGroup");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public DataTable getProjectGroupsById(string projectGroupID)
        {
            string strSql = "";
            string strOwnerGroupID;
            string strUserType;

            strOwnerGroupID = HttpContext.Current.Session[SysUtil.m_str_session_usergroupid].ToString();
            strUserType = HttpContext.Current.Session[SysUtil.m_str_session_usertype].ToString();

            if (ConnectDB())
            {
                strSql = "select * from ProjectGroup where ProjectGroupID='" + projectGroupID + "'";

                return m_objDB.getDataTable(strSql, "ProjectGroup");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public int insertProjectGroup(string projectGroupID, 
                                        string titleSubject, 
                                        string type,
                                        string projectClass,
                                        string applicant,
                                        string inventor,
                                        string client,
                                        string officerName,
                                        string title,
                                        string officerRole,
                                        string officerEmail)
        {
            //this.ODSProjectGroups.InsertMethod = "Insert";
            //this.ODSProjectGroups.InsertParameters["ProjectGroupID"].DefaultValue = this.txtProjectGroupID.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["Description"].DefaultValue = this.txtTitleSubject.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["OfficerName"].DefaultValue = this.txtOfficerName.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["OfficerRole"].DefaultValue = this.txtOfficerRole.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["Title"].DefaultValue = this.txtTitle.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["OfficerEmail"].DefaultValue = this.txtOfficerEmail.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["ProjectClass"].DefaultValue = this.txtProjectClass.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["Applicant"].DefaultValue = this.txtApplicant.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["Inventor"].DefaultValue = this.txtInventor.Text.ToString();
            //this.ODSProjectGroups.InsertParameters["Type"].DefaultValue = this.ddlType.SelectedValue.ToString();
            //this.ODSProjectGroups.InsertParameters["Client"].DefaultValue = this.txtClient.Text;
            //this.ODSProjectGroups.InsertParameters["TitleSubject"].DefaultValue = this.txtTitleSubject.Text.ToString();
            int ret = 0;
            string strSql = "";
            string strUserId;
            string strUserIp;
            string strOwnerGroupID;
            string strOwnerUserId;

            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;
            strOwnerGroupID = HttpContext.Current.Session[SysUtil.m_str_session_usergroupid].ToString();
            strOwnerUserId = HttpContext.Current.Session[SysUtil.m_str_session_userid].ToString();

            if (ConnectDB())
            {
                strSql = "insert into ProjectGroup(ProjectGroupID, TitleSubject, Type, ProjectClass, Applicant, Inventor, Client, OfficerName, Title, OfficerRole, OfficerEmail";
                strSql += ", CreateIp, CreateBy, CreateDate, UpdateIp, UpdateBy, UpdateDate, OwnerGroupID, OwnerUserId)";
                strSql += " values('" + projectGroupID + "','" + titleSubject + "','" + type + "','" + projectClass + "','";
                strSql += applicant + "','" + inventor + "','" + client + "','" + officerName + "','" + title + "','" + officerRole + "','" + officerEmail + "','";
                strSql += strUserIp + "','" + strUserId + "',getDate(),'" + strUserIp + "','" + strUserId + "',getDate(),'" + strOwnerGroupID + "'," + strOwnerUserId + ")";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
                ret = -1;
            }
            return ret;
        }


        public int deleteProjectGroup(string projectGroupID)
        {
            int ret = 0;
            string strUserId;
            string strUserIp;
            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;

            if (ConnectDB())
            {
                string strSql;
                strSql = "update ProjectGroup set ";
                strSql += " isDelete=1";
                strSql += ", DeleteBy='" + strUserId + "'";
                strSql += ", DeleteIP='" + strUserIp + "'";
                strSql += ", DeleteDate=getDate()";
                strSql += " where ProjectGroupID='" + projectGroupID + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);
                ret = objError.RetCode;
            }
            return ret;
        }


        public int trashProjectGroup(string projectGroupID)
        {
            int ret = 0;
            if (ConnectDB())
            {
                string strSQL;
                strSQL = "delete from ProjectGroup where ProjectGroupID='" + projectGroupID + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSQL, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            return ret;
        }


        public int restoreProjectGroup(string projectGroupID)
        {
            int ret = 0;
            if (ConnectDB())
            {
                string strSQL;
                strSQL = "update ProjectGroup set isDelete=0 where ProjectGroupID='" + projectGroupID + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSQL, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            return ret;
        }


        public int updateProjectGroup(string projectGroupID,
                                        string titleSubject,
                                        string type,
                                        string projectClass,
                                        string applicant,
                                        string inventor,
                                        string client,
                                        string officerName,
                                        string title,
                                        string officerRole,
                                        string officerEmail)
        {
            int ret = 0;
            string strSql = "";
            string strUserId;
            string strUserIp;
            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;

            if (projectGroupID == "" || projectGroupID == null)
            {
                return -1;
            }

            if (ConnectDB())
            {
                strSql = "update ProjectGroup set ";
                strSql += " TitleSubject='" + titleSubject + "'";
                strSql += ", Type='" + type + "'";
                strSql += ", ProjectClass='" + projectClass + "'";
                strSql += ", Applicant='" + applicant + "'";
                strSql += ", Inventor='" + inventor + "'";
                strSql += ", Client='" + client + "'";
                strSql += ", OfficerName='" + officerName + "'";
                strSql += ", Title='" + title + "'";
                strSql += ", OfficerRole='" + officerRole + "'";
                strSql += ", OfficerEmail='" + officerEmail + "'";
                strSql += ", UpdateBy='" + strUserId + "'";
                strSql += ", UpdateIp='" + strUserIp + "'";
                strSql += ", UpdateDate=getDate()";
                strSql += " where ProjectGroupID='" + projectGroupID + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
                ret = -1;
            }
            return ret;
        }
    }
}
